options compress=YES;
libname manasi ''; 

* USE ONLY HUN AND PAN FROM PARENTS' SSR RECORDS;
data manasi.ssr_parents_hunpan;
	set manasi.ssr_parents_full(keep=hun pan toa mft dob dobyy dod sex);
	if pan='*00000000' or pan='000000000' or pan=. then delete;
run;

* RETAIN UNIQUE HUN-PAN COMBINATIONS AND SORT BY HUN;
proc sort data=manasi.ssr_parents_hunpan out=manasi.ssr_parents_hunpan nodupkey;
	by hun pan;
run;

* SORT SUMMARY STATS BY SSN ;
proc sort data=manasi.kids_ssr_full_summ out=manasi.kids_ssr_full_summ;
	by hun;
run;

* MERGE IN SSI PAYMENT DATA FOR EACH KID;
data manasi.ssr_parents_wkidstats;
	merge 	
			manasi.ssr_parents_hunpan 
				(rename=(pan=par_ssn toa=par_toa mft=par_mft dob=par_dob dobyy=par_dobyy 
					dod=par_dod sex=par_sex) in=fromparx)
			
			manasi.kids_ssr_full_summ
				(keep = hun n_combpay_: 
				in=fromkidsx);

	by hun; 
	frompar = fromparx;
	fromkids = fromkidsx;
run;

proc tabulate data=manasi.ssr_parents_wkidstats;
	class frompar fromkids;
	table frompar, fromkids;
run;

* SORT BY PARENT SSN TO KEEP SIBLINGS TOGETHER ;
proc sort data=manasi.ssr_parents_wkidstats out=manasi.ssr_parents_wkidstats;
	by par_ssn hun;
run;

* GET RID OF UNKNOWN PARENT SSNs ;
data manasi.ssr_parents_wkidstats_wide;
	set manasi.ssr_parents_wkidstats;
	if par_ssn=. or par_ssn='000000000' then delete;
run;

* CREATE ID VARIABLE FOR KIDS;
data manasi.ssr_parents_wkidstats_wide;
	set manasi.ssr_parents_wkidstats_wide;
	kid_id + 1;
	by par_ssn;
	if first.par_ssn then kid_id=1;
run;

proc means data=manasi.ssr_parents_wkidstats_wide noprint;
	by par_ssn;
	var kid_id;
	output out=manasi.ssr_parents_wkidstats_kidid max(kid_id)=max_kidid;
run;

proc means mean median min max p75 p90 p95 p99 data=manasi.ssr_parents_wkidstats_kidid;
	var max_kidid;
run;

data manasi.ssr_parents_wkidstats_wide;
	merge 	
			manasi.ssr_parents_wkidstats_wide 
			manasi.ssr_parents_wkidstats_kidid
				(keep = par_ssn max_kidid);
	by par_ssn; 
run;

data manasi.ssr_parents_wkidstats_wide;
	set manasi.ssr_parents_wkidstats_wide;
	if kid_id<=7;
run;

* COLLAPSE TO PAN LEVEL TO GET ONE RECORD FOR EACH PARENT;
data manasi.ssr_parents_wkidstats_wide;
	set manasi.ssr_parents_wkidstats_wide;
	by par_ssn;

	keep 	par_ssn 
			kidhun1-kidhun7
			n_combpay_1983_1-n_combpay_1983_7 
			n_combpay_1984_1-n_combpay_1984_7
			n_combpay_1985_1-n_combpay_1985_7
			n_combpay_1986_1-n_combpay_1986_7
			n_combpay_1987_1-n_combpay_1987_7
			n_combpay_1988_1-n_combpay_1988_7
			n_combpay_1989_1-n_combpay_1989_7
			n_combpay_1990_1-n_combpay_1990_7
			n_combpay_1991_1-n_combpay_1991_7
			n_combpay_1992_1-n_combpay_1992_7
			n_combpay_1993_1-n_combpay_1993_7
			n_combpay_1994_1-n_combpay_1994_7
			n_combpay_1995_1-n_combpay_1995_7
			n_combpay_1996_1-n_combpay_1996_7
			n_combpay_1997_1-n_combpay_1997_7
			n_combpay_1998_1-n_combpay_1998_7
			n_combpay_1999_1-n_combpay_1999_7
			n_combpay_2000_1-n_combpay_2000_7
			n_combpay_2001_1-n_combpay_2001_7
			n_combpay_2002_1-n_combpay_2002_7
			n_combpay_2003_1-n_combpay_2003_7
			n_combpay_2004_1-n_combpay_2004_7
			n_combpay_2005_1-n_combpay_2005_7
			n_combpay_2006_1-n_combpay_2006_7
			n_combpay_2007_1-n_combpay_2007_7
			n_combpay_2008_1-n_combpay_2008_7
			n_combpay_2009_1-n_combpay_2009_7
			n_combpay_2010_1-n_combpay_2010_7
			n_combpay_2011_1-n_combpay_2011_7
			n_combpay_2012_1-n_combpay_2012_7
			;	

	retain 	kidhun1-kidhun7
			n_combpay_1983_1-n_combpay_1983_7 
			n_combpay_1984_1-n_combpay_1984_7
			n_combpay_1985_1-n_combpay_1985_7
			n_combpay_1986_1-n_combpay_1986_7
			n_combpay_1987_1-n_combpay_1987_7
			n_combpay_1988_1-n_combpay_1988_7
			n_combpay_1989_1-n_combpay_1989_7
			n_combpay_1990_1-n_combpay_1990_7
			n_combpay_1991_1-n_combpay_1991_7
			n_combpay_1992_1-n_combpay_1992_7
			n_combpay_1993_1-n_combpay_1993_7
			n_combpay_1994_1-n_combpay_1994_7
			n_combpay_1995_1-n_combpay_1995_7
			n_combpay_1996_1-n_combpay_1996_7
			n_combpay_1997_1-n_combpay_1997_7
			n_combpay_1998_1-n_combpay_1998_7
			n_combpay_1999_1-n_combpay_1999_7
			n_combpay_2000_1-n_combpay_2000_7
			n_combpay_2001_1-n_combpay_2001_7
			n_combpay_2002_1-n_combpay_2002_7
			n_combpay_2003_1-n_combpay_2003_7
			n_combpay_2004_1-n_combpay_2004_7
			n_combpay_2005_1-n_combpay_2005_7
			n_combpay_2006_1-n_combpay_2006_7
			n_combpay_2007_1-n_combpay_2007_7
			n_combpay_2008_1-n_combpay_2008_7
			n_combpay_2009_1-n_combpay_2009_7
			n_combpay_2010_1-n_combpay_2010_7
			n_combpay_2011_1-n_combpay_2011_7
			n_combpay_2012_1-n_combpay_2012_7
			;

	array 	akidhun(1:7) 		kidhun1-kidhun7 ; 
	array	acombpay_1983(1:7)	n_combpay_1983_1-n_combpay_1983_7;
	array	acombpay_1984(1:7)	n_combpay_1984_1-n_combpay_1984_7;
	array	acombpay_1985(1:7)	n_combpay_1985_1-n_combpay_1985_7;
	array	acombpay_1986(1:7)	n_combpay_1986_1-n_combpay_1986_7;
	array	acombpay_1987(1:7)	n_combpay_1987_1-n_combpay_1987_7;
	array	acombpay_1988(1:7)	n_combpay_1988_1-n_combpay_1988_7;
	array	acombpay_1989(1:7)	n_combpay_1989_1-n_combpay_1989_7;
	array	acombpay_1990(1:7)	n_combpay_1990_1-n_combpay_1990_7;
	array	acombpay_1991(1:7)	n_combpay_1991_1-n_combpay_1991_7;
	array	acombpay_1992(1:7)	n_combpay_1992_1-n_combpay_1992_7;
	array	acombpay_1993(1:7)	n_combpay_1993_1-n_combpay_1993_7;
	array	acombpay_1994(1:7)	n_combpay_1994_1-n_combpay_1994_7;
	array	acombpay_1995(1:7)	n_combpay_1995_1-n_combpay_1995_7;
	array	acombpay_1996(1:7)	n_combpay_1996_1-n_combpay_1996_7;
	array	acombpay_1997(1:7)	n_combpay_1997_1-n_combpay_1997_7;
	array	acombpay_1998(1:7)	n_combpay_1998_1-n_combpay_1998_7;
	array	acombpay_1999(1:7)	n_combpay_1999_1-n_combpay_1999_7;
	array	acombpay_2000(1:7)	n_combpay_2000_1-n_combpay_2000_7;
	array	acombpay_2001(1:7)	n_combpay_2001_1-n_combpay_2001_7;
	array	acombpay_2002(1:7)	n_combpay_2002_1-n_combpay_2002_7;
	array	acombpay_2003(1:7)	n_combpay_2003_1-n_combpay_2003_7;
	array	acombpay_2004(1:7)	n_combpay_2004_1-n_combpay_2004_7;
	array	acombpay_2005(1:7)	n_combpay_2005_1-n_combpay_2005_7;
	array	acombpay_2006(1:7)	n_combpay_2006_1-n_combpay_2006_7;
	array	acombpay_2007(1:7)	n_combpay_2007_1-n_combpay_2007_7;
	array	acombpay_2008(1:7)	n_combpay_2008_1-n_combpay_2008_7;
	array	acombpay_2009(1:7)	n_combpay_2009_1-n_combpay_2009_7;
	array	acombpay_2010(1:7)	n_combpay_2010_1-n_combpay_2010_7;
	array	acombpay_2011(1:7)	n_combpay_2011_1-n_combpay_2011_7;
	array	acombpay_2012(1:7)	n_combpay_2012_1-n_combpay_2012_7;


	if first.par_ssn then
	do;
		do i=1 to 7;
			akidhun(i)=.;
			acombpay_1983(i) = .;	
			acombpay_1984(i) = .;	
			acombpay_1985(i) = .;	
			acombpay_1986(i) = .;	
			acombpay_1987(i) = .;	
			acombpay_1988(i) = .;	
			acombpay_1989(i) = .;	
			acombpay_1990(i) = .;	
			acombpay_1991(i) = .;	
			acombpay_1992(i) = .;	
			acombpay_1993(i) = .;	
			acombpay_1994(i) = .;	
			acombpay_1995(i) = .;	
			acombpay_1996(i) = .;	
			acombpay_1997(i) = .;	
			acombpay_1998(i) = .;	
			acombpay_1999(i) = .;	
			acombpay_2000(i) = .;	
			acombpay_2001(i) = .;
			acombpay_2002(i) = .;
			acombpay_2003(i) = .;
			acombpay_2004(i) = .;
			acombpay_2005(i) = .;
			acombpay_2006(i) = .;
			acombpay_2007(i) = .;
			acombpay_2008(i) = .;
			acombpay_2009(i) = .;
			acombpay_2010(i) = .;
			acombpay_2011(i) = .;
			acombpay_2012(i) = .;
		end;
	end;

	akidhun(kid_id) = hun ;	
	acombpay_1983(kid_id) = n_combpay_1983;
	acombpay_1984(kid_id) = n_combpay_1984;
	acombpay_1985(kid_id) = n_combpay_1985;
	acombpay_1986(kid_id) = n_combpay_1986;
	acombpay_1987(kid_id) = n_combpay_1987;
	acombpay_1988(kid_id) = n_combpay_1988;
	acombpay_1989(kid_id) = n_combpay_1989;
	acombpay_1990(kid_id) = n_combpay_1990;
	acombpay_1991(kid_id) = n_combpay_1991;
	acombpay_1992(kid_id) = n_combpay_1992;
	acombpay_1993(kid_id) = n_combpay_1993;
	acombpay_1994(kid_id) = n_combpay_1994;
	acombpay_1995(kid_id) = n_combpay_1995;
	acombpay_1996(kid_id) = n_combpay_1996;
	acombpay_1997(kid_id) = n_combpay_1997;
	acombpay_1998(kid_id) = n_combpay_1998;
	acombpay_1999(kid_id) = n_combpay_1999;
	acombpay_2000(kid_id) = n_combpay_2000;
	acombpay_2001(kid_id) = n_combpay_2001;
	acombpay_2002(kid_id) = n_combpay_2002;
	acombpay_2003(kid_id) = n_combpay_2003;
	acombpay_2004(kid_id) = n_combpay_2004;
	acombpay_2005(kid_id) = n_combpay_2005;
	acombpay_2006(kid_id) = n_combpay_2006;
	acombpay_2007(kid_id) = n_combpay_2007;
	acombpay_2008(kid_id) = n_combpay_2008;
	acombpay_2009(kid_id) = n_combpay_2009;
	acombpay_2010(kid_id) = n_combpay_2010;
	acombpay_2011(kid_id) = n_combpay_2011;
	acombpay_2012(kid_id) = n_combpay_2012;

	if last.par_ssn then output;

run;

* MERGE (SAMPLE) SSNs WITH PARENT-KID FILE ;
proc sort data=manasi.ssr_parents_hunpan out=manasi.ssr_parents_hunpan nodupkey;
	by pan hun;
run;

data manasi.ssr_kids_wsiblings_wide;
	merge 	
			manasi.ssr_parents_hunpan 
				(rename=(pan=par_ssn) keep=pan hun in=fromkidsx)
			
			manasi.ssr_parents_wkidstats_wide
				(rename=(kidhun1=sibssn1 kidhun2=sibssn2 kidhun3=sibssn3 
					kidhun4=sibssn4 kidhun5=sibssn5 kidhun6=sibssn6 kidhun7=sibssn7) 
					in=fromparx);

	by par_ssn; 
	frompar = fromparx;
	fromkids = fromkidsx;

run;

proc tabulate data=manasi.ssr_kids_wsiblings_wide;
	class frompar fromkids;
	table frompar, fromkids;
run;

* SORT BY (SAMPLE) SSN TO GET KID'S SIBLINGS ALL TOGETHER;
proc sort data=manasi.ssr_kids_wsiblings_wide out=manasi.ssr_kids_wsiblings_wide ;
	by hun;
run;
